<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class BuildingTable extends BaseTable
{
    protected static $view = 'V_ASSET_BUILDING';
    protected static $table = 'ASSET_BUILDING';
    protected static $pk = 'BUILDING_NO';
    protected static $configTable = 'ASSET_BUILDING_CONFIG';

    static public function table(){
        $sql = "(
        select 
        b.BUILDING_NO
        ,to_char(b.INPUT_DATE,'yyyy-mm-dd') INPUT_DATE
        ,b.CHECK_TYPE_ID
        ,b.ASSET_TYPE_ID
        ,d1.CNAME94 ASSET_TYPE
        ,cast(b.BUILDING_NAME as varchar2(75)) BUILDING_NAME
        ,b.BUILDING_UNIT
        ,b.BUILDING_SIZE
        ,b.CONSTRU_ID
        ,d2.CONSTRU
        ,b.BUILDING_MEASUREMENT
        ,b.OFFICE_SIZE
        ,b.MEETING_SIZE
        ,b.CAR_SIZE
        ,b.CANTEEN_SIZE
        ,b.DISTRIBUTION_SIZE
        ,b.LAB_SIZE
        ,b.USE_SIZE
        ,b.UNDER_SIZE
        ,b.ACCOUNT_CERT_NO
        ,b.PROPERTYRIGHT_ID
        ,d3.PROPERTYRIGHT
        ,b.OWNER_CERT
        ,b.OWNER_CERT_NO
        ,to_char(b.CERT_GET_DATE,'yyyy-mm-dd') CERT_GET_DATE
        ,b.BUILDING_FEE
        ,b.AVG_FEE
        ,b.FINANCE_GET_FEE
        ,b.BUSINESS_GET_FEE
        ,b.BUSINESS_BUDGET_GET_FEE
        ,b.OTHER_FEE
        ,b.OTHER_FINANCE_FEE
        ,b.GETTYPE_ID
        ,d4.GETTYPE
        ,to_char(b.GET_DATE,'yyyy-mm-dd') GET_DATE
        ,b.SELF_SIZE
        ,b.SELF_FEE
        ,b.BORROW_SIZE
        ,b.BORROW_FEE
        ,b.RENTAL_SIZE
        ,b.RENTAL_FEE
        ,b.INVESTMENT_SIZE
        ,b.INVESTMENT_FEE
        ,b.GUARANTEE_SIZE
        ,b.GUARANTEE_FEE
        ,b.ELSE_SIZE
        ,b.ELSE_FEE
        ,b.PURCHASE_ID
        ,d5.PURCHASE
        ,b.STATUS_ID
        ,d6.STATUS
        ,cast(b.BORROW_DEPART as varchar2(75)) BORROW_DEPART
        ,cast(b.RENTAL_DEPART as varchar2(75)) RENTAL_DEPART
        ,b.DEPRECIATE_STATUS_ID
        ,b.DEPRECIATE_WAY
        ,b.USE_MONTH
        ,b.DEPRECIATED_MONTH
        ,b.DEPRECIATE_PERCENTAGE
        ,b.REDUCE_PRE
        ,b.BUILDING_AREA
        ,b.SELF_DEPART_USE_PERIOD
        ,b.COLLEGE_ID
        ,d7.COLLEGE
        ,cast(b.REMARK as varchar2(300) ) REMARK
        ,b.USER_ID
        ,d8.NAME USER_NAME
        ,to_char(b.OPER_DATETIME,'yyyy-mm-dd') OPER_DATETIME
        ,b.VALUETYPE_ID
        ,d9.valuetype
        ,b.GISMAP_ID
        ,b.FLOORNUM
        from app_asset.asset_building b   
        left join app_asset.ASSET_DICT_TYPE94TO2010 d1 on d1.ccode94=b.asset_type_id
        left join app_asset.ASSET_DICT_CONSTRU d2 on d2.CONSTRU_ID=b.CONSTRU_ID
        left join app_asset.ASSET_DICT_PROPERTYRIGHT d3 on d3.PROPERTYRIGHT_ID=b.PROPERTYRIGHT_ID
        left join app_asset.ASSET_DICT_GETTYPE d4 on d4.GETTYPE_ID=b.GETTYPE_ID
        left join app_asset.ASSET_DICT_PURCHASE d5 on d5.PURCHASE_ID=b.PURCHASE_ID
        left join app_asset.ASSET_DICT_STATUS d6 on d6.STATUS_ID=b.STATUS_ID
        left join app_asset.DICT_COLLEGE d7 on d7.COLLEGE_ID=b.COLLEGE_ID
        left join app_asset.EMPLOYEE d8 on d8.STAFF_ID=b.USER_ID
        left join app_asset.ASSET_DICT_VALUETYPE d9 on d9.valuetype_id=b.VALUETYPE_ID
        )";
        return Db::table($sql);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
        return $data[self::$pk];
    }

    static public function addImport($data){
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(static::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(static::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }


    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.static::$pk.',5,4)) as NUM')
            ->where([['SUBSTR('.static::$pk.',1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'资产编号','field'=>'BUILDING_NO'],
            ['title'=>'资产分类代码','field'=>'ASSET_TYPE_ID'],
            ['title'=>'资产分类','field'=>'ASSET_TYPE'],
            ['title'=>'资产名称','field'=>'BUILDING_NAME'],
            ['title'=>'清查编号','field'=>'CHECK_TYPE_ID'],
            ['title'=>'计量单位','field'=>'BUILDING_UNIT'],
            ['title'=>'建筑面积','field'=>'BUILDING_SIZE'],
            ['title'=>'建筑结构','field'=>'CONSTRU'],
            ['title'=>'构筑物计量','field'=>'BUILDING_MEASUREMENT'],
            ['title'=>'办公室面积','field'=>'OFFICE_SIZE'],
            ['title'=>'会议室面积','field'=>'MEETING_SIZE'],
            ['title'=>'车库面积','field'=>'CAR_SIZE'],
            ['title'=>'食堂面积','field'=>'CANTEEN_SIZE'],
            ['title'=>'配电室面积','field'=>'DISTRIBUTION_SIZE'],
            ['title'=>'机房面积','field'=>'LAB_SIZE'],
            ['title'=>'使用面积','field'=>'USE_SIZE'],
            ['title'=>'地下面积','field'=>'UNDER_SIZE'],
            ['title'=>'会计凭证号','field'=>'ACCOUNT_CERT_NO'],
            ['title'=>'产权形式','field'=>'PROPERTYRIGHT_ID'],
            ['title'=>'权属证明','field'=>'OWNER_CERT'],
            ['title'=>'权属证号','field'=>'OWNER_CERT_NO'],
            ['title'=>'发证时间','field'=>'CERT_GET_DATE'],
            ['title'=>'价值','field'=>'BUILDING_FEE'],
            ['title'=>'均价','field'=>'AVG_FEE'],
            ['title'=>'财政拨款','field'=>'FINANCE_GET_FEE'],
            ['title'=>'事业收入','field'=>'BUSINESS_GET_FEE'],
            ['title'=>'事业收入：预算外收入','field'=>'BUSINESS_BUDGET_GET_FEE'],
            ['title'=>'其他资金','field'=>'OTHER_FEE'],
            ['title'=>'其他资金：财政性结余资金','field'=>'OTHER_FINANCE_FEE'],
            ['title'=>'取得方式','field'=>'GETTYPE'],
            ['title'=>'竣工日期','field'=>'GET_DATE'],
            ['title'=>'自用面积','field'=>'SELF_SIZE'],
            ['title'=>'自用价值','field'=>'SELF_FEE'],
            ['title'=>'出借面积','field'=>'BORROW_SIZE'],
            ['title'=>'出借价值','field'=>'BORROW_FEE'],
            ['title'=>'出租面积','field'=>'RENTAL_SIZE'],
            ['title'=>'出租价值','field'=>'RENTAL_FEE'],
            ['title'=>'对外投资面积','field'=>'INVESTMENT_SIZE'],
            ['title'=>'对外投资价值','field'=>'INVESTMENT_FEE'],
            ['title'=>'担保面积','field'=>'GUARANTEE_SIZE'],
            ['title'=>'担保价值','field'=>'GUARANTEE_FEE'],
            ['title'=>'其他面积','field'=>'ELSE_SIZE'],
            ['title'=>'其他价值','field'=>'ELSE_FEE'],
            ['title'=>'采购组织形式','field'=>'PURCHASE'],
            ['title'=>'使用状况','field'=>'STATUS'],
            ['title'=>'出借单位','field'=>'BORROW_DEPART'],
            ['title'=>'出租单位','field'=>'RENTAL_DEPART'],
            ['title'=>'折旧状态','field'=>'DEPRECIATE_STATUS_ID'],
            ['title'=>'折旧方法','field'=>'DEPRECIATE_WAY'],
            ['title'=>'使用年限/月份','field'=>'USE_MONTH'],
            ['title'=>'已提折旧月数','field'=>'DEPRECIATED_MONTH'],
            ['title'=>'残值率','field'=>'DEPRECIATE_PERCENTAGE'],
            ['title'=>'减值准备','field'=>'REDUCE_PRE'],
            ['title'=>'座落位置','field'=>'BUILDING_AREA'],
            ['title'=>'投入本单位使用时间','field'=>'SELF_DEPART_USE_PERIOD'],
            ['title'=>'使用\管理部门','field'=>'COLLEGE'],
            ['title'=>'备注','field'=>'REMARK']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const BUILDING_FIELD_LIST = [
        "BUILDING_NO","ASSET_TYPE_ID","BUILDING_NAME","OLD_BUILDING_NO","LOCATION","BUDGET_NO","PROPERTYRIGHT_ID","GETTYPE_ID","GET_DATE","PRE_USE_LIFE","POWERTYPE_ID","OWNER_CERT","OWNER_CERT_NO","OWNER_CERT_YEAR","CERT_OWNER_NAME","USE_TYPE","CONSTRU_ID","COMPLETION_DATE","CERT_GET_DATE","ENTER_TYPE_ID","ACCOUNT_CERT_NO","ENTER_DATE","USEDIRECTION_ID","STATUS_ID","START_USE_DATE","COLLEGE_ID","VALUETYPE_ID","BUILDING_NUM","BUILDING_FEE","FINANCE_FEE","NON_FINANCE_FEE","AVG_FEE","BUILD_SIZE","HEAT_SIZE","SELF_SIZE","BORROW_SIZE","RENTAL_SIZE","INVESTMENT_SIZE","ELSE_SIZE","SELF_FEE","BORROW_FEE","RENTAL_FEE","INVESTMENT_FEE","ELSE_FEE","DEPRECIATE_STATUS_ID","DEPRECIATE_WAY","DEPRECIATE_LIFE","REDUCE_PRE","DEPRECIATE_PER_MON","DEPRECIATE_PERCENTAGE","DEPRECIATED_MONTH","ACC_DEPRECIATE","REMARK"
    ];

    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionBuilding();
        if (!empty($listCondition['BUILDING_NO'])) {
            $value = $listCondition['BUILDING_NO'];
            $condition[] = ['BUILDING_NO','like',"$value%"];
        }
        if (!empty($listCondition['BUILDING_NAME'])) {
            $value = $listCondition['BUILDING_NAME'];
            $condition[] = ['BUILDING_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['PROPERTYRIGHT_ID'])) {
            $value = $listCondition['PROPERTYRIGHT_ID'];
            $condition[] = ['PROPERTYRIGHT_ID','=',$value];
        }
        if (!empty($listCondition['POWERTYPE_ID'])) {
            $value = $listCondition['POWERTYPE_ID'];
            $condition[] = ['POWERTYPE_ID','=',$value];
        }
        if (!empty($listCondition['GETTYPE_ID'])) {
            $value = $listCondition['GETTYPE_ID'];
            $condition[] = ['GETTYPE_ID','=',$value];
        }
        if (!empty($listCondition['CONSTRU_ID'])) {
            $value = $listCondition['CONSTRU_ID'];
            $condition[] = ['CONSTRU_ID','=',$value];
        }
        if (!empty($listCondition['ENTER_TYPE_ID'])) {
            $value = $listCondition['ENTER_TYPE_ID'];
            $condition[] = ['ENTER_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['USEDIRECTION_ID'])) {
            $value = $listCondition['USEDIRECTION_ID'];
            $condition[] = ['USEDIRECTION_ID','=',$value];
        }
        if (!empty($listCondition['STATUS_ID'])) {
            $value = $listCondition['STATUS_ID'];
            $condition[] = ['STATUS_ID','=',$value];
        }
        if (!empty($listCondition['COLLEGE_ID'])) {
            $value = $listCondition['COLLEGE_ID'];
            $condition[] = ['COLLEGE_ID','=',$value];
        }
        if (!empty($listCondition['VALUETYPE_ID'])) {
            $value = $listCondition['VALUETYPE_ID'];
            $condition[] = ['VALUETYPE_ID','=',$value];
        }
        if (!empty($listCondition['DEPRECIATE_STATUS_ID'])) {
            $value = $listCondition['DEPRECIATE_STATUS_ID'];
            $condition[] = ['DEPRECIATE_STATUS_ID','=',$value];
        }

        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','<=',$value];
        }

        if (!empty($listCondition['BUILDING_FEE_START'])) {
            $value = $listCondition['BUILDING_FEE_START'];
            $condition[] = ['BUILDING_FEE','>=',$value];
        }
        if (!empty($listCondition['BUILDING_FEE_END'])) {
            $value = $listCondition['BUILDING_FEE_END'];
            $condition[] = ['BUILDING_FEE','<=',$value];
        }
        if (!empty($listCondition['BUILD_SIZE_START'])) {
            $value = $listCondition['BUILD_SIZE_START'];
            $condition[] = ['BUILD_SIZE','>=',$value];
        }
        if (!empty($listCondition['BUILD_SIZE_END'])) {
            $value = $listCondition['BUILD_SIZE_END'];
            $condition[] = ['BUILD_SIZE','<=',$value];
        }

        return $condition;
    }
}